﻿CREATE FUNCTION [dbo].[Collection_PromiseToPay](
	@LoanID int
)RETURNS TABLE 
RETURN 
WITH N AS(
SELECT MAX (LoanID) LoanID,
	SUM(CASE StatusID WHEN dbo.fs_StateID__Notes__PromisedToPay() THEN 1 ELSE 0 END) PTP,
	SUM(CASE StatusID WHEN dbo.fs_StateID__Notes__PaymentRecieved() THEN 1 ELSE 0 END) PR,
	SUM(CASE StatusID WHEN dbo.fs_StateID__Notes__BrokenPromise() THEN 1 ELSE 0 END) BP
FROM t_Note WITH(nolock) WHERE LoanID = @LoanID AND NoteDate > DATEADD(dd,-180,GETDATE())
)
SELECT ISNULL(PTP+PR+BP,0) [PTPs:],ISNULL(PTP,0) [Open],ISNULL(PR,0) Paid,ISNULL(BP,0) Broken
--,DPD30,DPD60,DPD90 [DPD90+]
--,
--(SELECT CONVERT(int,ROUND(AVG(CASE WHEN DPD > 0 THEN DPD ELSE 0.0 END),0))
-- FROM usix.v_Payment WHERE [Id #] = CF_E.UsixID AND CompanyID = CF_E.CompanyID_Raw AND [# of Payments]>0
--)DPDA
FROM N
--CROSS JOIN usix.t_CustomerFile_Export CF_E
--INNER JOIN usix.v_PaymentDPDs DPD ON DPD.[Id #] = CF_E.UsixID AND DPD.CompanyID = CF_E.CompanyID_Raw
--WHERE CF_E.LoanID = @LoanID

